﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace XinLife.Core.Helper
{
    public static class ExcelUtil
    {
        /// <summary>
        /// let blob = new Blob([res], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
        /// let objUrl = URL.createObjectURL(blob);
        /// window.location.href = objUrl;
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static byte[] ExportExcel(DataTable dataTable)
        {
            //建立Excel
            byte[] result = null;
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("Data");
                //workSheet.Workbook.Properties.Title = "inventory";//设置excel的标题
                workSheet.Workbook.Properties.Author = "凉生";//作者
                //workSheet.Workbook.Properties.Comments = "this is a test";//备注
                workSheet.Workbook.Properties.Company = "zw";//公司

                //在Excel文件中添加内容
                workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);

                // 格式标题-白色-粗体， 单元格背景色
                using (ExcelRange r = workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
                {
                    r.AutoFilter = false;
                    r.Style.Font.Color.SetColor(Color.White);
                    r.Style.Font.Bold = true;
                    r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    r.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#1fb5ad"));
                }

                // 小单元格的内容自动调整宽度
                int columnIndex = 1;
                foreach (DataColumn item in dataTable.Columns)
                {
                    ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row + 1, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                    string maxValue = columnCells.Max(cell => (cell.Value ?? "").ToString());
                    int maxLength = maxValue.Count();
                    if (maxLength < 66) workSheet.Column(columnIndex).AutoFit();//自动调整宽度
                    else workSheet.Column(columnIndex).Style.WrapText = true; //自动换行
                    if (Regex.IsMatch(maxValue, @"^[+-]?\d*[.]?\d*$"))//验证是否为数字字符串
                    {
                        workSheet.Column(columnIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    }
                    if (item.DataType.Equals(Type.GetType("System.DateTime")))//验证是否是时间
                    {
                        workSheet.Column(columnIndex).AutoFit();
                        workSheet.Column(columnIndex).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
                    }
                    columnIndex++;
                }

                // 格式化单元格-添加边框 
                using (ExcelRange r = workSheet.Cells[2, 1, workSheet.Dimension.End.Row, workSheet.Dimension.End.Column])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(Color.Black);
                    r.Style.Border.Left.Color.SetColor(Color.Black);
                    r.Style.Border.Right.Color.SetColor(Color.Black);
                }

                result = package.GetAsByteArray();

            }
            return result;
        }
    }
}
